package gecko.model;

import gecko.view.swing.GESTION.VGestionArchive;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;

import com.mysql.jdbc.PreparedStatement;

public class Requete {

	private int act;

	public Object[][] consulte() throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_contrat) FROM contrat");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}
		ResultSet result = state
				.executeQuery("SELECT lbl_contrat, typecontrat, nom_fourn, lib_service, date, fin_contrat, delai, signataire, actif "
						+ "FROM contrat, fournisseur, service, typecontrat "
						+ "WHERE contrat.id_typecontrat = typecontrat.id_typecontrat "
						+ "AND contrat.id_fourn = fournisseur.id_fourn "
						+ "AND service.id_service = contrat.id_service "
						+ "ORDER BY id_contrat;");
		int i = 0;
		Object[][] donnees = new Object[nbligne][9];
		while (result.next()) {
			donnees[i][0] = result.getString(1);
			donnees[i][1] = result.getString(2);
			donnees[i][2] = result.getString(3);
			donnees[i][3] = result.getString(4);
			donnees[i][4] = result.getString(5);
			donnees[i][5] = result.getString(7)+ " mois";
			donnees[i][6] = result.getString(6);
			donnees[i][7] = result.getString(8);
			String actif = null;
			if (result.getString(9).equals("1")) {
				actif = "En cours";
			} else
				actif = "Archive";
			donnees[i][8] = actif;
			i++;

		}
		return donnees;
	}

	public Object[][] consulteService() throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_service) FROM service");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}

		ResultSet result = state
				.executeQuery("SELECT id_service, lib_service FROM service ORDER BY id_service;");
		int i = 0;
		Object[][] donnees = new Object[nbligne][2];
		while (result.next()) {
			donnees[i][0] = Integer.toString(result.getInt(1));
			donnees[i][1] = result.getString(2);
			i++;

		}
		return donnees;
	}

	public void ajoutService(String resultSaisie) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("INSERT INTO service (lib_service) VALUES ('"
				+ resultSaisie + "')");
	}

	public void modifService(String resultSaisie, String idServ)
			throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("UPDATE service SET `lib_service` = '"
				+ resultSaisie + "' WHERE `SERVICE`.`lib_service` = '" + idServ
				+ "'");
	}
	
	
	
	//RECHERCHE MAXIME
	public Object[][] afficheRepRech(String fourn, String serv, String typo, boolean actif) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		
		if (actif == true) {
			act = 0;
		} 
		
		else {
			act = 1;
			
		}
		
		ResultSet resultnbligne = state.executeQuery("SELECT COUNT(id_contrat) FROM contrat");
		int nbligne = 0;
			
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}
			
		
/*
		ResultSet resultnbligne = state
				.executeQuery("SELECT nom_fourn, lib_service,typecontrat, actif FROM contrat, fournisseur, service, typecontrat WHERE contrat.id_typecontrat = typecontrat.id_typecontrat AND typecontrat.typecontrat = '"
						+ typo
						+ "' AND contrat.id_fourn = fournisseur.id_fourn AND fournisseur.nom_fourn ='"
						+ fourn
						+ "' AND service.id_service = contrat.id_service AND service.lib_service = '"
						+ serv
						+ "' AND contrat.actif = '" + act + "';");
		int nbligne = 0;
		int j = 0;

		while (resultnbligne.next()) {
			String fournreq = resultnbligne.getString(1);
			String servreq = resultnbligne.getString(2);
			String typoreq = resultnbligne.getString(3);
			boolean actifreq = Boolean.valueOf(resultnbligne.getString(4));
			if (fournreq==fourn && servreq==serv && typoreq==typo && actifreq==actif) {
				j++;
			}
			nbligne = j;
		}
		
		*/
	
		
		ResultSet resulttous = state.executeQuery("SELECT typecontrat, nom_fourn,lib_service FROM contrat, fournisseur, service, typecontrat");
		while (resulttous.next()) {
			if(fourn == "tous"){
				fourn=resulttous.getString(2);
				System.out.println(fourn);
			}
			
			if(serv == "tous"){
				serv=resulttous.getString(3);
				System.out.println(serv);
			}
			
			if(typo == "tous"){
				typo=resulttous.getString(1);
				System.out.println(typo);
			}
		}

		ResultSet result = state
				.executeQuery("SELECT id_contrat, lbl_contrat, typecontrat, nom_fourn, date, lib_service, delai, signataire, actif FROM contrat, fournisseur, service, typecontrat"
						+ " WHERE contrat.id_typecontrat = typecontrat.id_typecontrat "
						+ "AND typecontrat.typecontrat = '"
						+ typo
						+ "' AND contrat.id_fourn = fournisseur.id_fourn "
						+ "AND fournisseur.nom_fourn ='"
						+ fourn
						+ "' AND service.id_service = contrat.id_service"
						+ " AND service.lib_service = '"
						+ serv
						+ "' AND contrat.actif = '" + act + "';");

		int i = 0;
		Object[][] donnees = new Object[nbligne][9];
		while (result.next()) {
			donnees[i][0] = Integer.toString(result.getInt(1));
			donnees[i][1] = result.getString(2);
			donnees[i][2] = result.getString(3);
			donnees[i][3] = result.getString(4);
			donnees[i][4] = result.getString(5);
			donnees[i][5] = result.getString(6);
			donnees[i][6] = result.getString(7) + " mois";
			donnees[i][7] = result.getString(8);
			String Messactif = null;
			if (result.getString(9).equals("1")) {
				Messactif = "En cours";
			} else
				Messactif = "Archiv�";
			donnees[i][8] = Messactif;
			i++;
		}

			return donnees;
	}
	

/*
	public Object[][] afficheRep(String fourn, String serv, String typo, String sign, String lbl, boolean actif) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		if (actif == true) {
			act = 0;
		} else {
			act = 1;
		}

		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_contrat) FROM contrat");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}

		ResultSet result = state
				.executeQuery("SELECT id_contrat, lbl_contrat, typecontrat, nom_fourn, date, lib_service, delai, signataire, actif FROM contrat, fournisseur, service, typecontrat WHERE contrat.id_typecontrat = typecontrat.id_typecontrat AND typecontrat.typecontrat = '"
						+ typo
						+ "' AND contrat.id_fourn = fournisseur.id_fourn AND fournisseur.nom_fourn ='"
						+ fourn
						+ "' AND service.id_service = contrat.id_service AND service.lib_service = '"
						+ serv
						+ "' AND contrat.signataire = '"
						+ sign
						+ "' AND contrat.lbl_contrat = '"
						+ lbl
						+ "' AND contrat.actif = '" + act + "';");

		int i = 0;
		Object[][] donnees = new Object[nbligne][9];
		while (result.next()) {
			donnees[i][0] = Integer.toString(result.getInt(1));
			donnees[i][1] = result.getString(2);
			donnees[i][2] = result.getString(3);
			donnees[i][3] = result.getString(4);
			donnees[i][4] = result.getString(5);
			donnees[i][5] = result.getString(6);
			donnees[i][6] = result.getString(7) + " mois";
			donnees[i][7] = result.getString(8);
			String Messactif = null;
			if (result.getString(9).equals("1")) {
				Messactif = "En cours";
			} else
				Messactif = "Archiv�";
			donnees[i][8] = Messactif;
			i++;
		}
		return donnees;
	}

*/
	
	public JComboBox<String> comboFournisseurrech() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		
		combo.addItem("Tous");
		ResultSet result = state
				.executeQuery("SELECT nom_fourn FROM fournisseur ORDER BY nom_fourn;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}
	
	public JComboBox<String> comboTypologierech() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		
		combo.addItem("Tous");
		ResultSet result = state
				.executeQuery("SELECT typecontrat FROM typecontrat ORDER BY typecontrat;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}
	
	public JComboBox<String> comboServicerech() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		combo.addItem("Tous");
		ResultSet result = state
				.executeQuery("SELECT lib_service FROM service ORDER BY lib_service;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}
	
	
	
	public JComboBox<String> comboService() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet result = state
				.executeQuery("SELECT lib_service FROM service ORDER BY lib_service;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}

	public JComboBox<String> comboFournisseur() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		
		ResultSet result = state
				.executeQuery("SELECT nom_fourn FROM fournisseur ORDER BY nom_fourn;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}
	
	public JComboBox<String> comboUtilisateur() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		
		ResultSet result = state
				.executeQuery("SELECT nom, prenom FROM user ORDER BY nom;");
		while (result.next()) {
			combo.addItem(result.getString(1)+" "+result.getString(2));
		}
		return combo;
	}

	public JComboBox<String> comboTypologie() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		
		ResultSet result = state
				.executeQuery("SELECT typecontrat FROM typecontrat ORDER BY typecontrat;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}

	public JComboBox<String> comboSignataire() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet result = state
				.executeQuery("SELECT signataire FROM contrat;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}

	public JComboBox<String> comboContrat() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet result = state
				.executeQuery("SELECT id_contrat, date, lbl_contrat, lib_service FROM contrat, service WHERE contrat.id_service = service.id_service ORDER BY date");
		while (result.next()) {
			combo.addItem(result.getString(1)+" | "+result.getString(2)+" | "+result.getString(3)+" | "+result.getString(4));
		}
		return combo;
	}
	
	public JComboBox<String> comboLibelleCont() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet result = state
				.executeQuery("SELECT lbl_contrat FROM contrat;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}

	public Object[][] consulteFournisseur() throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_fourn) FROM fournisseur");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}

		ResultSet result = state
				.executeQuery("SELECT id_fourn, nom_fourn, numero_fourn, rue_fourn, cp_fourn, ville_fourn FROM fournisseur ORDER BY id_fourn;");
		int i = 0;
		Object[][] donnees = new Object[nbligne][6];
		while (result.next()) {
			donnees[i][0] = Integer.toString(result.getInt(1));
			donnees[i][1] = result.getString(2);
			donnees[i][2] = result.getString(3);
			donnees[i][3] = result.getString(4);
			donnees[i][4] = result.getString(5);
			donnees[i][5] = result.getString(6);
			i++;

		}
		return donnees;
	}

	public void ajoutFournisseur(String resultNomFourn,
			String resultNumeroFourn,String resultRueFourn, String resultCPFourn,
			String resultVilleFourn) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("INSERT INTO fournisseur (nom_fourn, numero_fourn, rue_fourn, cp_fourn, ville_fourn) VALUES ('"
				+ resultNomFourn
				+ "', '"
				+ resultNumeroFourn
				+ "', '"
				+ resultRueFourn
				+ "' , '"
				+ resultCPFourn + "', '" + resultVilleFourn + "')");
	}

	public String recupService(String Service) throws SQLException {
		Connect con = new Connect();
		Statement serv = con.dbConnect().createStatement();
		ResultSet IDservice = serv.executeQuery("SELECT id_service FROM service WHERE lib_service = '"+Service+"'");
		String idServ = null;
		while (IDservice.next()) {
			idServ  = Integer.toString(IDservice.getInt(1));
        }
		return idServ;
	}

	public String recupFourn(String Fourn) throws SQLException {
		Connect con = new Connect();
		Statement fou = con.dbConnect().createStatement();
		ResultSet IDfourn = fou
				.executeQuery("SELECT id_fourn FROM fournisseur WHERE (nom_fourn = '"+Fourn+"')");
		String idFour = null;
		while (IDfourn.next()) {
			idFour  = Integer.toString(IDfourn.getInt(1));
        }
		return idFour;
	}

	public String recupTypo(String Typo) throws SQLException {
		Connect con = new Connect();
		Statement ty = con.dbConnect().createStatement();
		ResultSet IDtypo = ty
				.executeQuery("SELECT id_typecontrat FROM typecontrat WHERE (typecontrat = '"+Typo+"')");
		String idTypo = null;
		while (IDtypo.next()) {
			idTypo  = Integer.toString(IDtypo.getInt(1));
        }
		return idTypo;
	}
	
		

	public void ajoutContrat(String resultService, String resultFourn,
			String resultTypo, String resultDelai, String resultSign,
			String resultDate, String resultFin, String resultLibelle, String resultUrl)
			throws SQLException {
		Connect con = new Connect();
		//if (resultLibelle.equals("") || resultSign.equals("")){
		//	JOptionPane.showMessageDialog(null, "Veuillez renseigner tous les champs !","Erreur",0);
		//} else {
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("INSERT INTO contrat (id_typecontrat, date, delai, fin_contrat, actif, etat, id_service, id_fourn, lbl_contrat, signataire, url) VALUES ('"
				+ resultTypo
				+ "', '"
				+ resultDate
				+ "', '"
				+ resultDelai
				+ "', '"
				+ resultFin
				+ "', 1, 1, '"
				+ resultService
				+ "', '"
				+ resultFourn
				+ "','"
				+ resultLibelle
				+ "', '"
				+ resultSign
				+ "', '"
				+ resultUrl
				+ "')");
		//}
	}

	public void modifContrat(String resultContrat, String resultService,
			String resultFourn, String resultTypo, String resultDelai,
			String resultSign, String resultDate, String resultFin,
			String resultLibelle, String resultUrl) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		//if (resultLibelle.equals("") || resultSign.equals("")){
		//	JOptionPane.showMessageDialog(null, "Veuillez renseigner tous les champs !","Erreur",0);
		//} else {
			state.executeUpdate("UPDATE contrat SET `id_typecontrat` = '"
					+ resultTypo + "', `date` = '" + resultDate
					+ "', `delai` = '" + resultDelai + "', `fin_contrat` = '"
					+ resultFin + "', `actif` = 1, `etat` = 1, `id_service` = '" + resultService
					+ "', `id_fourn` = '" + resultFourn + "', `lbl_contrat` = '"
					+ resultLibelle + "', `signataire` = '" + resultSign
					+ "'  WHERE `id_contrat` = '" + resultContrat
					+ "'");		
		//}
	}
	
	public Vector afficheRep(String fourn, String serv, String typo, boolean actif) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		if (actif == true){
			act = 0;
		}
		else {act = 1;}
		
		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_contrat) FROM contrat");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}
		String requete = "SELECT id_contrat, lbl_contrat, typecontrat, nom_fourn, date, lib_service, delai, signataire, actif FROM contrat, fournisseur, service, typecontrat WHERE contrat.id_typecontrat = typecontrat.id_typecontrat AND typecontrat.typecontrat = '"+fourn+"' AND contrat.id_fourn = fournisseur.id_fourn AND fournisseur.nom_fourn ='"+serv+"' AND service.id_service = contrat.id_service AND service.lib_service = '"+typo+"' AND contrat.actif = '"+act+"';" ;
		ResultSet result = state
				.executeQuery(requete);
		
		//int i = 0;
		Object[] tab = new Object[9];
		Vector<Object[]> donnees = new Vector();
		while (result.next()) {
			tab[0] = Integer.toString(result.getInt(1));
			tab[1] = result.getString(2);
			tab[2] = result.getString(3);
			tab[3] = result.getString(4);
			tab[4] = result.getString(5);
			tab[5] = result.getString(6);
			tab[6] = result.getString(7) + " mois";
			tab[7] = result.getString(8);
			String Messactif = null;
			if (result.getString(9).equals("1")){
				Messactif = "En cours";
			} else Messactif = "Archiv�";
			tab[8] = Messactif;
			//i++;
			donnees.add(tab);
		}
		return donnees;
	}

	public Object[][] consulteTypo() throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_typecontrat) FROM typecontrat");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}

		ResultSet result = state
				.executeQuery("SELECT id_typecontrat, typecontrat FROM typecontrat ORDER BY id_typecontrat;");
		int i = 0;
		Object[][] donnees = new Object[nbligne][2];
		while (result.next()) {
			donnees[i][0] = Integer.toString(result.getInt(1));
			donnees[i][1] = result.getString(2);
			i++;

		}
		return donnees;
	}

	public void ajoutTypo(String resultSaisie) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("INSERT INTO typecontrat (typecontrat) VALUES ('"
				+ resultSaisie + "')");
	}

	public void modifTypo(String resultSaisie, String idTypo)
			throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("UPDATE typecontrat SET `typecontrat` = '"
				+ resultSaisie + "' WHERE `typecontrat`.`typecontrat` = '"
				+ idTypo + "'");
	}

	public void ajoutUtilisateur(String resultNom, String resultPrenom, String resultNumero, String resultRue, String resultCp, String resultVille, String resultFonction, String resultService, String resultMdp)  throws SQLException{
		
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		String idService = this.recupService(resultService);
		state.executeUpdate("INSERT INTO user (nom, prenom, numero, rue, cp, ville, fonction, id_service, motdepasse) VALUES ('"+resultNom+"','"+resultPrenom+"','"+resultNumero+"','"+resultRue+"','"+resultCp+"','"+resultVille+"','"+resultFonction+"','"+idService+"','"+resultMdp+"')");
		
	}
	
	/*public JComboBox<String> comboUtilisateur() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet result = state
				.executeQuery("SELECT nom FROM user ORDER BY nom;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	} */
	
	public String recupUtil(String Util) throws SQLException {
		Connect con = new Connect();
		Statement ut = con.dbConnect().createStatement();
		ResultSet utilisateur = ut
				.executeQuery("SELECT id_user FROM user WHERE (`nom` = '"
						+ Util + "')");
		String idUtil = null; 
		while(utilisateur.next()){
			idUtil = Integer.toString(utilisateur.getInt(1));
		}
		return utilisateur.toString();
	}
	
	
	public void modifUtilisateur(String nom, String resultNom, String resultPrenom, String resultNumero, String resultRue, String resultCp, String resultVille, String resultFonction, String resultService, String resultMdp)  throws SQLException{
		
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		//String idUser = this.recupUtil(nom);
		//state.executeUpdate("INSERT INTO user (nom, prenom, numero, rue, cp, ville, fonction, motdepasse) VALUES ('"+resultNom+"', '"+resultPrenom+"','"+resultNumero+"','"+resultRue+"','"+resultCp+"','"+resultVille+"','"+resultFonction+"','"+idService+"','"+resultMdp+"'");
		state.executeUpdate("UPDATE user SET `nom`='"+resultNom+"',`prenom`='"+resultPrenom+"',`numero`='"+resultNumero+"',`rue`='"+resultRue+"',`cp`='"+resultCp+"',`ville`='"+resultVille+"',`fonction`='"+resultFonction+"',`motdepasse`='"+resultMdp+"' WHERE  `user`.`id_user` = '"
				+ nom + "'");
	}
	
	
	public JComboBox<String> comboTypo() throws SQLException {
		JComboBox<String> combo = new JComboBox<String>();
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet result = state
				.executeQuery("SELECT typecontrat FROM typecontrat ORDER BY typecontrat;");
		while (result.next()) {
			combo.addItem(result.getString(1));
		}
		return combo;
	}

	public Object[][] consulteUtilisateur() throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_user) FROM user");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}

		ResultSet result = state
				.executeQuery("SELECT id_user, nom, prenom, rue, lib_service, fonction, motdepasse FROM user, service WHERE user.id_service = service.id_service ORDER BY id_user;");
		int i = 0;
		Object[][] donnees = new Object[nbligne][7];
		while (result.next()) {
			donnees[i][0] = Integer.toString(result.getInt(1));
			donnees[i][1] = result.getString(2);
			donnees[i][2] = result.getString(3);
			donnees[i][3] = result.getString(4);
			donnees[i][4] = result.getString(5);
			donnees[i][5] = result.getString(6);
			donnees[i][6] = result.getString(7);
			i++;

		}
		return donnees;
	}

	public Object[][] alerte() throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		ResultSet resultnbligne = state
				.executeQuery("SELECT id_contrat,DATEDIFF(DATE(now()),DATE(CONCAT(YEAR(now()),'-00-00'))),DATEDIFF(fin_contrat,DATE(CONCAT(YEAR(fin_contrat),'-00-00'))),delai FROM contrat WHERE etat<>0  ORDER BY id_contrat;");
		int nbligne = 0;
		int jour = 0;
		int i = 0;
		int j = 0;

		while (resultnbligne.next()) {
			jour = Math.abs(resultnbligne.getInt(3) - resultnbligne.getInt(2))
					- resultnbligne.getInt(4) * 30;
			if (jour < 16 && jour > 0) {
				j++;
			}
			nbligne = j;
		}

		ResultSet result = state
				.executeQuery("SELECT id_contrat, typecontrat, nom_fourn, DATE(date),lib_service,"
						+ "DATEDIFF(DATE(now()),DATE(CONCAT(YEAR(now()),'-00-00'))),"
						+ "DATEDIFF(fin_contrat,DATE(CONCAT(YEAR(fin_contrat),'-00-00'))),etat,delai,lbl_contrat,fin_contrat, delai, signataire "
						+ "FROM contrat, fournisseur, service, typecontrat "
						+ "WHERE contrat.id_typecontrat = typecontrat.id_typecontrat "
						+ "AND contrat.id_fourn = fournisseur.id_fourn "
						+ "AND service.id_service = contrat.id_service "
						+ "ORDER BY id_contrat;");

		Object[][] donnees = new Object [nbligne][11];

		while (result.next()) {
			
			jour = Math.abs(result.getInt(7) - result.getInt(6))
					- result.getInt(9) * 30;
			if (result.getInt(8) != 0 && jour < 16 && jour > 0 ) {
				donnees[i][0] = Integer.toString(result.getInt(1));
				donnees[i][1] = result.getString(10);
				donnees[i][2] = result.getString(2);
				donnees[i][3] = result.getString(3);
				donnees[i][4] = result.getString(4);
				donnees[i][5] = result.getString(11);
				donnees[i][6] = result.getString(12)+ " moi(s)";
				donnees[i][7] = result.getString(13);
				donnees[i][8] = result.getString(5);
				donnees[i][9] = jour + " jour(s)";
				donnees[i][10]=false;
				i++;
			}

		}
		return donnees;
	}

	public boolean verifresilie() throws SQLException {
		boolean statut = false;
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();

		ResultSet resultnbligne = state
				.executeQuery("SELECT COUNT(id_contrat) FROM contrat");
		int nbligne = 0;
		if (resultnbligne.next()) {
			nbligne = resultnbligne.getInt(1);
		}

		ResultSet result = state
				.executeQuery("SELECT id_contrat, typecontrat, nom_fourn, date, delai,lib_service FROM contrat, fournisseur, service, typecontrat WHERE contrat.id_typecontrat = typecontrat.id_typecontrat AND contrat.id_fourn = fournisseur.id_fourn AND service.id_service = contrat.id_service ORDER BY id_contrat;");
		int i = 0;
		Object[][] donnees = new Object[nbligne][6];
		Object[][] res = new Object[nbligne][2];
		Object resilie;
		ResultSet resultres = state
				.executeQuery("SELECT id_contrat,etat FROM contrat;");

		/*
		 * while (resultres.next()) { res[i][0] = resultres.getString(1);
		 * res[i][1] = resultres.getBoolean(2); resilie = res[i][1];
		 * System.out.println(resilie); i++; if ((boolean) (resilie=true)) {
		 * statut = true; }
		 * 
		 * }
		 */
		return statut;
	}

	public boolean verifmdp(String mdpSaisie) throws SQLException {
		boolean statut = false;
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		ResultSet result = state
				.executeQuery("SELECT COUNT(id_user) FROM user");
		int nbligne = 0;
		if (result.next()) {
			nbligne = result.getInt(1);

		}
		ResultSet resultmdp = state
				.executeQuery("SELECT motdepasse FROM user;");
		String[][] motdepasse = new String[nbligne][1];
		String mdp = null;
		int i = 0;
		while (resultmdp.next()) {
			motdepasse[i][0] = resultmdp.getString(1);
			mdp = motdepasse[i][0];			
			i++;
			if (mdp.equals(mdpSaisie)) {
				statut = true;
			}
		} 
		return statut;
	}

	public boolean verifAdmin(String userSaisie) throws SQLException {
		boolean statut = false;
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		ResultSet result = state
				.executeQuery("SELECT COUNT(id_user) FROM user");
		int nbligne = 0;
		if (result.next()) {
			nbligne = result.getInt(1);

		}
		ResultSet resultadmin = state
				.executeQuery("SELECT nom,fonction FROM user;");
		String[][] tab = new String[nbligne][2];
		String fonctionadmin = "Administrateur";
		String admin = null;
		String user = null;
		int i = 0;

		while (resultadmin.next()) {
			tab[i][0] = resultadmin.getString(1);
			tab[i][1] = resultadmin.getString(2);
			user = tab[i][0];
			admin = tab[i][1];
			i++;
			if (user.equals(userSaisie) && admin.equals(fonctionadmin)) {
				statut = true;
			}
		}

		return statut;
	}

	public boolean verifUser(String userSaisie) throws SQLException {
		boolean statut = false;
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		ResultSet result = state
				.executeQuery("SELECT COUNT(id_user) FROM user");
		int nbligne = 0;
		if (result.next()) {
			nbligne = result.getInt(1);

		}
		ResultSet resultadmin = state
				.executeQuery("SELECT nom,fonction FROM user;");
		String[][] tab = new String[nbligne][2];
		String fonctionchefdeservice = "Chef de Service";
		String chefdeservice = null;
		String user = null;
		int i = 0;

		while (resultadmin.next()) {
			tab[i][0] = resultadmin.getString(1);
			tab[i][1] = resultadmin.getString(2);
			user = tab[i][0];
			chefdeservice = tab[i][1];
			i++;
			if (user.equals(userSaisie)
					&& chefdeservice.equals(fonctionchefdeservice)) {
				statut = true;
			}
		}

		return statut;
	}

/*
	public void annulerAlerte(Object value) throws SQLException {
		Connect con = new Connect();
		Statement state = con.dbConnect().createStatement();
		state.executeUpdate("UPDATE contrat SET `etat` = '1' WHERE id_contrat = '"+value+"'");
	}
*/

}
